/**
 * Copyright 2005 WUZEWEN. All rights reserved.
 * WZW PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
 
package com.kingcore.pda.database.controller;

                 
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.kingcore.framework.context.ApplicationContext;
import com.kingcore.pda.base.controller.PdaAction;


/**
 * <p>Description: oracle/sqlserver2000数据库表信息生成 *.woo.table 文件到目录："E:/ProgramXP/test/" </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: Kingnor Soft</p>
 * @author	WUZEWEN
 * @date	2005-7-27
 * @version	1.0
 * @see		Object#equals(java.lang.Object)
 * @see		Object#hashCode()
 * @see		HashMap
 * @since	JDK5
 */


public class ExportTableDataAction extends PdaAction {
	
    public ActionForward executeAction(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
				throws ServletException, IOException,Exception{
    	String action = request.getParameter("Action");
    	if( action==null) {	// 创建表文件的默认行为
    		return this.createTableDataFile( mapping, form, request, response);
    	}
    	
    	return null;
    	
	}
	
	/**
	 * <p>该类要求线程安全。</p>
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	private synchronized ActionForward createTableDataFile(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {


		// 类成员
		String dbType = "" ;	//数据库类型
		String driver = "" ;	//驱动类
		String url = "" ;		//连接url属性
		String username="";		
		String password="";
		String maxconn="";		//最大连接数
		String path="" ;		//导出的sql文件存放路径
		String tableCondition="";	//表的特征条件
		String is_tblname="";	//表名
		String poolName="";		//连接池名字

		clearErrors();
		//copyright protect here
		String serverLoginName=System.getProperty("user.name");
		if(!( serverLoginName.equalsIgnoreCase("WUZEWEN")
		    ||serverLoginName.equalsIgnoreCase("WZWEN")
		    ||serverLoginName.equalsIgnoreCase("Zewin")
		    ||serverLoginName.equalsIgnoreCase("Zeven")
		    ||serverLoginName.equalsIgnoreCase("Administrator") )){

			addErrors("err.illegalAuthorization");
			this.saveError(request);
			return mapping.findForward("fail");	
		}

		is_tblname = request.getParameter("table0");
		String isAllTable = request.getParameter("isAllTable");	//是否是所有的表
		Vector vector=new Vector();	//表集合
		//获取配置文件值
		java.util.Properties pro=new java.util.Properties();
		try
		{			
			pro.load(new java.io.FileInputStream(request.getRealPath("")+"/WEB-INF/conf/pda-project.properties"));
			String conf_prefix=null;
			conf_prefix=request.getParameter("projectCode");	//从客户端取编号
			//System.out.println ("aa"+conf_prefix);
			if(conf_prefix==null || conf_prefix.trim().equals("null") || conf_prefix.trim().equals("")){
				//System.out.println ("faddd");
				conf_prefix=pro.getProperty("use-config");
			}else{
				//conf_prefix=conf_prefix.substring( 0, conf_prefix.indexOf("_") );
			}
			
			conf_prefix=(conf_prefix==null)?"":(conf_prefix+".");
			//System.out.println (conf_prefix);
			dbType=pro.getProperty( conf_prefix+"dbType");
			username=pro.getProperty( conf_prefix+"username");
			poolName=pro.getProperty( conf_prefix+"poolName");
			//driver=pro.getProperty( conf_prefix+"driver");
			//url=pro.getProperty( conf_prefix+"url");
			//password=pro.getProperty( conf_prefix+"password");
			//maxconn=pro.getProperty( conf_prefix+"maxconn");
			tableCondition=pro.getProperty( conf_prefix+"tableCondition");
			path=pro.getProperty( conf_prefix+"expPath");
			
			if(tableCondition==null || tableCondition.trim().equals("")){
				tableCondition="1=1" ;
			}
			path += File.separator+"data";	//wzw 2005-10-12 数据新开目录
			System.out.println ("generate files to Export.:"+path);
			if(path==null || path.trim().equals("")){
				System.out.println ("user default path");
				path="E:/ProgramXP/test/data" ;
			}
			java.io.File file = new java.io.File( path );
			if(!file.exists()){
				file.mkdir();
				System.out.println("create path success,path is "+path);
			}
		}catch(java.io.IOException e){
			System.out.println ("cann't load properties.properties file"+e.getMessage());
			return mapping.findForward("success");
		}

		log.info( "success to get all parameters. now will begin connect to database,using database pool name is：" + poolName );
		//创建数据库连接并且获取信息
		String sql ;
		Connection conn = null ;
		Statement  stmt  = null ;
		Statement  stmt2  = null ;
		Statement  stmt3  = null ;
		ResultSet  rs =null;
		ResultSet  rs2 =null;
		ResultSet  rs3 =null;
		ResultSetMetaData rsmd=null;
		int loops = 0 ;
		try{
			//得到数据库连接
			//Class.forName(driver) ;
			//conn = DriverManager.getConnection(url, username, password) ;
			conn=ApplicationContext.getInstance().getDataSourceManager().getConnection(poolName);
			//if( testResultSet(conn) ) return ;	
			stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);//创建主表Statement

			//是单个表还是所有表
			if(isAllTable.trim().endsWith("true")){
				//根据不同的数据库类型结构设置不同sql
				if(dbType.equals("oracle")){
					sql=" select TABLE_NAME,TABLE_TYPE,COMMENTS from user_tab_comments t where ("+tableCondition+") or t.TABLE_NAME='DEMOTABLE' order by t.TABLE_NAME " ;
					
				}else if(dbType.equalsIgnoreCase("sqlServer")){
					sql="SELECT t.id AS tblId, t.name AS TABLE_NAME, 'TABLE' AS TABLE_TYPE,t2.value AS COMMENTS, t.xtype FROM sysobjects t LEFT OUTER JOIN sysproperties t2 ON t.id = t2.id AND t2.type = 3 AND t2.name = 'MS_Description' WHERE ( (t.xtype='U' or t.xtype='S') )  order by TABLE_NAME" ;
					
				}else{
					addErrors("cann't create a select statement for some type database!");
					this.saveError(request);
					return mapping.findForward("fail");					
				}	
				rs = stmt.executeQuery(sql);//查询主表数据
				while(rs.next()){
					vector.add(rs.getString("TABLE_NAME"));
				}	
				
			}else{
				vector.add(is_tblname);	//单个表
			}

			for (int k = 0; k<vector.size(); k++){
				is_tblname=(String)vector.get(k);
				//System.out.println("dfljshfjksfkl" + sql);
				sql="select * from "+is_tblname;
				System.out.println (sql);
				
				java.io.File objFile = new java.io.File(path+File.separator+is_tblname+".woo.data" );
				//判断文件是否存在
				if(objFile.exists())//文件存在
					objFile.delete() ;
				objFile.createNewFile()  ;
				java.io.PrintWriter pw = new java.io.PrintWriter(new java.io.FileOutputStream(objFile)) ;
				
				rs = stmt.executeQuery(sql);//查询主表数据
				rsmd=rs.getMetaData();
	
				String colName[]=new String[rsmd.getColumnCount()];
				String colType[]=new String[rsmd.getColumnCount()];
				String colNames="";
				for(int i=1;i<=rsmd.getColumnCount();i++){	//设置表的字段
					//System.out.println ("aa="+i);
					colName[i-1]=rsmd.getColumnName(i);
					colNames+=colName[i-1]+",";
					colType[i-1]=rsmd.getColumnTypeName(i);
				}
				colNames=colNames.substring(0,colNames.length()-1);
				
				for(int i=0;i<colType.length;i++){
					System.out.println ("colType="+colType[i]);
				}
				for(int i=0;i<colName.length;i++){
					System.out.println ("colName="+colName[i]);
				}
				
				String oneRowDatas="";
				pw.println("-- this sql script is created by PDA(Java),WUZEWEN on "+( new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date() ) )+",DBMS is "+dbType ) ;
				pw.println("delete from "+is_tblname+";") ;
				//rs.getObject(这里从1开始);数组下标从0开始.
				while(rs.next()){
					oneRowDatas="";
					colNames="";
					for(int i=0;i<colType.length;i++){
						if(rs.getObject(i+1)==null){
							oneRowDatas+="null,";
							colNames+=colName[i]+",";						
						}else if(colType[i].toUpperCase().indexOf("CHAR")>-1){	//char,varchar,varchar2
							oneRowDatas+="'"+rs.getString(i+1).trim()+"',";	//加入trim
							colNames+=colName[i]+",";
						}else if(colType[i].toUpperCase().indexOf("NUMBER")>-1){
							oneRowDatas+=rs.getFloat(i+1)+",";
							colNames+=colName[i]+",";
						}else if(colType[i].toUpperCase().indexOf("DATE")>-1){
							//timestampe,日期和时间要使用一个空格或者:号分隔
							if((rs.getDate(i+1).toString().trim().indexOf(" ")>0)||(rs.getDate(i+1).toString().trim().indexOf(":")>0)){
								oneRowDatas+="to_date('"+rs.getDate(i+1)+"','yyyy-mm-dd hh24:mi:ss'),";							
							}else{
								oneRowDatas+="to_date('"+rs.getDate(i+1)+"','YYYY-MM-DD'),";
							}
							colNames+=colName[i]+",";
						}
					}
					oneRowDatas=oneRowDatas.substring(0,oneRowDatas.length()-1);
					colNames=colNames.substring(0,colNames.length()-1);
					pw.println("insert into "+is_tblname+"("+colNames+")") ;
					pw.println("\tvalues("+oneRowDatas+");") ;
				}			
				pw.close();	
				
			}			
			System.out.println ("导出成功------！");
			
		}catch(Exception e){
			throw new Exception("处理产生错误：" + e.getMessage() );
		}finally{
			try{
				if(rs!=null)
					rs.close();
				if(rs2!=null)
					rs2.close();
				if(rs3!=null)
					rs3.close();
				if(stmt!=null)
					stmt.close();
				if(stmt2!=null)
					stmt2.close();
				if(stmt3!=null)
					stmt3.close();
				if(conn!=null)
					conn.close();
			}catch(java.sql.SQLException e){
				throw new Exception ("error when close rs and stmt");
			}
		}
		addErrors("msg.createDataFile.success");
		this.saveError(request);
		return mapping.findForward("success");
		
	}


	public static boolean testResultSet(Connection conn) throws SQLException{ 
		//test rs  
		String sql="select csm,cszwm,type from fx_bb_bbcsxx where SFZCX='1' and  bbbh='0606'" ; 
		Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY ) ;
		ResultSet rs = stmt.executeQuery(sql);
		
			System.out.println("---------2005-8-4 ") ;
		if(rs.next()){
			rs.last() ;
			System.out.println("---------2005-8-4 " + rs.getRow() );
		
			rs.beforeFirst() ;
			System.out.println("---------2005-8-4");
		}
		 
		//rs=stmt.executeQuery(sql);
		int i=0;
		String csm = new String() ;
		boolean isFirstRow = true ;
		while(rs.next()){
			System.out.println ("value = " +  rs.getString("cszwm") );
		}
			System.out.println("---------2005-8-4 end ok");
		return true ;
	}	
}

